﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml;


namespace SqlHelper
{
    public sealed class SqlHelper
    {
        private static string _ConnectionString = "Data Source=192.168.1.49;DataBase=UFDATA_009_2022;uid=bb;pwd=qwerasdf@;timeout=60000";

        private SqlHelper()
        {
        }

        private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
        {
            if ((commandParameters != null) && (parameterValues != null))
            {
                if (commandParameters.Length != parameterValues.Length)
                {
                    throw new ArgumentException("Parameter count does not match Parameter Value count.");
                }
                int index = 0;
                int length = commandParameters.Length;
                while (true)
                {
                    if (index >= length)
                    {
                        break;
                    }
                    commandParameters[index].Value = parameterValues[index];
                    index++;
                }
            }
        }

        private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
        {
            foreach (SqlParameter parameter in commandParameters)
            {
                if ((parameter.Direction == ParameterDirection.InputOutput) && (parameter.Value == null))
                {
                    parameter.Value = DBNull.Value;
                }
                command.Parameters.Add(parameter);
            }
        }

        public static DataSet ExecuteDataset(string sqlQueryString) =>
            ExecuteDataset(ConnectionString, CommandType.Text, sqlQueryString);

        public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText) =>
            ExecuteDataset(connection, null, commandType, commandText);

        public static DataSet ExecuteDataset(SqlConnection connection, string spName, params object[] parameterValues) =>
            ExecuteDataset(connection, null, spName, parameterValues);

        public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText) =>
            ExecuteDataset(connectionString, commandType, commandText, null);


        public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) =>
            ExecuteDataset(connection, null, commandType, commandText, commandParameters);

        public static DataSet ExecuteDataset(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText) =>
            ExecuteDataset(connection, transaction, commandType, commandText, null);

        public static DataSet ExecuteDataset(SqlConnection connection, SqlTransaction transaction, string spName, params object[] parameterValues)
        {
            DataSet set;
            if ((parameterValues == null) || (parameterValues.Length <= 0))
            {
                set = ExecuteDataset(connection, transaction, CommandType.StoredProcedure, spName);
            }
            else
            {
                SqlParameter[] spParameterSet = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);
                AssignParameterValues(spParameterSet, parameterValues);
                set = ExecuteDataset(connection, transaction, CommandType.StoredProcedure, spName, spParameterSet);
            }
            return set;
        }

        public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            DataSet set;
            SqlConnection connection = new SqlConnection(connectionString);
            try
            {
                connection.Open();
                set = ExecuteDataset(connection, commandType, commandText, commandParameters);
            }
            finally
            {
                if (!ReferenceEquals(connection, null))
                {
                    connection.Dispose();
                }
            }
            return set;
        }

        public static DataSet ExecuteDataset(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            SqlCommand command = new SqlCommand();
            command.CommandTimeout = 0;
            PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters);
            DataSet dataSet = new DataSet();
            new SqlDataAdapter(command).Fill(dataSet);
            connection.Close();
            return dataSet;
        }

        public static int ExecuteNonQuery(string sql) =>
            ExecuteNonQuery(ConnectionString, CommandType.Text, sql);

        public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText) =>
            ExecuteNonQuery(connection, null, commandType, commandText);

        public static int ExecuteNonQuery(SqlConnection connection, string spName, params object[] parameterValues) =>
            ExecuteNonQuery(connection, null, spName, parameterValues);

        public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText) =>
            ExecuteNonQuery(connectionString, commandType, commandText, null);

        public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues)
        {
            int num;
            if ((parameterValues == null) || (parameterValues.Length <= 0))
            {
                num = ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
            }
            else
            {
                SqlParameter[] spParameterSet = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
                AssignParameterValues(spParameterSet, parameterValues);
                num = ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, spParameterSet);
            }
            return num;
        }

        public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) =>
            ExecuteNonQuery(connection, null, commandType, commandText, commandParameters);

        public static int ExecuteNonQuery(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText) =>
            ExecuteNonQuery(connection, transaction, commandType, commandText, null);

        public static int ExecuteNonQuery(SqlConnection connection, SqlTransaction transaction, string spName, params object[] parameterValues)
        {
            int num;
            if ((parameterValues == null) || (parameterValues.Length <= 0))
            {
                num = ExecuteNonQuery(connection, transaction, CommandType.StoredProcedure, spName);
            }
            else
            {
                SqlParameter[] spParameterSet = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);
                AssignParameterValues(spParameterSet, parameterValues);
                num = ExecuteNonQuery(connection, transaction, CommandType.StoredProcedure, spName, spParameterSet);
            }
            return num;
        }

        public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            int num;
            SqlConnection connection = new SqlConnection(connectionString);
            try
            {
                connection.Open();
                num = ExecuteNonQuery(connection, commandType, commandText, commandParameters);
            }
            finally
            {
                if (!ReferenceEquals(connection, null))
                {
                    connection.Dispose();
                }
            }
            return num;
        }

        public static int ExecuteNonQuery(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            
                SqlCommand command = new SqlCommand();
            command.CommandTimeout = 0;
            PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters);
                return command.ExecuteNonQuery();
           
        }

        public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText) =>
            ExecuteReader(connection, null, commandType, commandText);

        public static SqlDataReader ExecuteReader(SqlConnection connection, string spName, params object[] parameterValues) =>
            ExecuteReader(connection, null, spName, parameterValues);

        public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText) =>
            ExecuteReader(connectionString, commandType, commandText, null);

        public static SqlDataReader ExecuteReader(string connectionString, string spName, params object[] parameterValues)
        {
            SqlDataReader reader;
            if ((parameterValues == null) || (parameterValues.Length <= 0))
            {
                reader = ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
            }
            else
            {
                SqlParameter[] spParameterSet = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
                AssignParameterValues(spParameterSet, parameterValues);
                reader = ExecuteReader(connectionString, CommandType.StoredProcedure, spName, spParameterSet);
            }
            return reader;
        }

        public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) =>
            ExecuteReader(connection, null, commandType, commandText, commandParameters);

        public static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText) =>
            ExecuteReader(connection, transaction, commandType, commandText, null);

        public static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, string spName, params object[] parameterValues)
        {
            SqlDataReader reader;
            if ((parameterValues == null) || (parameterValues.Length <= 0))
            {
                reader = ExecuteReader(connection, transaction, CommandType.StoredProcedure, spName);
            }
            else
            {
                SqlParameter[] spParameterSet = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);
                AssignParameterValues(spParameterSet, parameterValues);
                reader = ExecuteReader(connection, transaction, CommandType.StoredProcedure, spName, spParameterSet);
            }
            return reader;
        }

        public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            SqlDataReader reader;
            SqlConnection connection = new SqlConnection(connectionString);
            connection.Open();
            try
            {
                reader = ExecuteReader(connection, null, commandType, commandText, commandParameters, SqlConnectionOwnership.Internal);
            }
            catch
            {
                connection.Close();
                throw;
            }
            return reader;
        }

        public static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) =>
            ExecuteReader(connection, transaction, commandType, commandText, commandParameters, SqlConnectionOwnership.External);

        private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
        {
            SqlCommand command = new SqlCommand();
            PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters);
            return ((connectionOwnership != SqlConnectionOwnership.External) ? command.ExecuteReader(CommandBehavior.CloseConnection) : command.ExecuteReader());
        }

        public static object ExecuteScalar(string commandText) =>
            ExecuteScalar(ConnectionString, CommandType.Text, commandText);

        public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText) =>
            ExecuteScalar(connection, null, commandType, commandText);

        public static object ExecuteScalar(SqlConnection connection, string spName, params object[] parameterValues) =>
            ExecuteScalar(connection, null, spName, parameterValues);

        public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText) =>
            ExecuteScalar(connectionString, commandType, commandText, null);

        public static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues)
        {
            object obj2;
            if ((parameterValues == null) || (parameterValues.Length <= 0))
            {
                obj2 = ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
            }
            else
            {
                SqlParameter[] spParameterSet = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
                AssignParameterValues(spParameterSet, parameterValues);
                obj2 = ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, spParameterSet);
            }
            return obj2;
        }

        public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) =>
            ExecuteScalar(connection, null, commandType, commandText, commandParameters);

        public static object ExecuteScalar(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText) =>
            ExecuteScalar(connection, transaction, commandType, commandText, null);

        public static object ExecuteScalar(SqlConnection connection, SqlTransaction transaction, string spName, params object[] parameterValues)
        {
            object obj2;
            if ((parameterValues == null) || (parameterValues.Length <= 0))
            {
                obj2 = ExecuteScalar(connection, transaction, CommandType.StoredProcedure, spName);
            }
            else
            {
                SqlParameter[] spParameterSet = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);
                AssignParameterValues(spParameterSet, parameterValues);
                obj2 = ExecuteScalar(connection, transaction, CommandType.StoredProcedure, spName, spParameterSet);
            }
            return obj2;
        }

        public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            object obj2;
            SqlConnection connection = new SqlConnection(connectionString);
            try
            {
                connection.Open();
                obj2 = ExecuteScalar(connection, commandType, commandText, commandParameters);
            }
            finally
            {
                if (!ReferenceEquals(connection, null))
                {
                    connection.Dispose();
                }
            }
            return obj2;
        }

        public static object ExecuteScalar(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            SqlCommand command = new SqlCommand();
            PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters);
            return command.ExecuteScalar();
        }

        public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText) =>
            ExecuteXmlReader(connection, null, commandType, commandText);

        public static XmlReader ExecuteXmlReader(SqlConnection connection, string spName, params object[] parameterValues) =>
            ExecuteXmlReader(connection, null, spName, parameterValues);

        public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) =>
            ExecuteXmlReader(connection, null, commandType, commandText, commandParameters);

        public static XmlReader ExecuteXmlReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText) =>
            ExecuteXmlReader(connection, transaction, commandType, commandText, null);

        public static XmlReader ExecuteXmlReader(SqlConnection connection, SqlTransaction transaction, string spName, params object[] parameterValues)
        {
            XmlReader reader;
            if ((parameterValues == null) || (parameterValues.Length <= 0))
            {
                reader = ExecuteXmlReader(connection, transaction, CommandType.StoredProcedure, spName);
            }
            else
            {
                SqlParameter[] spParameterSet = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);
                AssignParameterValues(spParameterSet, parameterValues);
                reader = ExecuteXmlReader(connection, transaction, CommandType.StoredProcedure, spName, spParameterSet);
            }
            return reader;
        }

        public static XmlReader ExecuteXmlReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            SqlCommand command = new SqlCommand();
            PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters);
            return command.ExecuteXmlReader();
        }

        private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters)
        {
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            command.Connection = connection;
            command.CommandText = commandText;
            if (!ReferenceEquals(transaction, null))
            {
                command.Transaction = transaction;
            }
            command.CommandType = commandType;
            if (commandParameters != null)
            {
                AttachParameters(command, commandParameters);
            }
        }

        public static string ConnectionString
        {
            get
            {
                string str;
                if (!ReferenceEquals(_ConnectionString, null))
                {
                    str = _ConnectionString;
                }
                else
                {
                    try
                    {
                        _ConnectionString = "Data Source=192.168.1.49;DataBase=UFDATA_009_2022;uid=bb;pwd=qwerasdf@";
       
                    }
                    catch
                    {
                        _ConnectionString = "Data Source=192.168.1.49;DataBase=UFDATA_009_2022;uid=bb;pwd=qwerasdf@";
      
                    }
                    str = _ConnectionString;
                }
                return str;
            }
        }

        private enum SqlConnectionOwnership
        {
            Internal,
            External
        }
    }
}

